package laborum2011.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import laborum2011.excepcion.DAOExcepcion;
import laborum2011.modelo.*;
import laborum2011.util.ConexionBD;



public class PostulanteDAO extends BaseDAO{

	public int insertar(Postulante po) throws DAOExcepcion {
		System.out.println("PostulanteDAO: insertar(Postulante vo)");
		String query = "INSERT INTO postulante(id_persona,nacionalidad,estado_civil,foto,estado) " +
				       "VALUES (?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		System.out.println("id "+ po.getId_persona());
		ResultSet rs = null;
		int idp =0;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, po.getId_persona());
			stmt.setString(2, po.getNacionalidad());
			stmt.setString(3, po.getEstado_civil());
			stmt.setString(4, po.getFoto());
			stmt.setInt(5, po.getEstado());
			int i = stmt.executeUpdate();
			
			query = "SELECT LAST_INSERT_ID()";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				idp = rs.getInt(1);
			}


			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return idp;
	}
	
	public Collection<Postulante> listar() throws DAOExcepcion {
		System.out.println("PostulanteDAO: listar()");
		Collection<Postulante> c = new ArrayList<Postulante>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			String query = "SELECT id_postulante, nacionalidad, estado_civil, foto,estado " 
						  +"FROM postulante where estado=1";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Postulante vo = new Postulante();
				vo.setId_persona(rs.getInt("id_persona"));
				vo.setNacionalidad(rs.getString("nacionalidad"));
				vo.setEstado_civil(rs.getString("estado_civil"));
				vo.setFoto(rs.getString("foto"));
				vo.setEstado(rs.getInt("estado"));
				c.add(vo);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return c;
	}	

	public void actualizar(Postulante po) throws DAOExcepcion {
		System.out.println("PostulanteDAO: actualizar(Postulante vo)");
		String query = "update postulante set  nacionalidad   = ?, " +
				"    estado_civil   = ?, foto  = ? " +
				"     where id_postulante=? and estado = 1";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			
			stmt.setString(1, po.getNacionalidad());
			stmt.setString(2, po.getEstado_civil());
			stmt.setString(3, po.getFoto());
			stmt.setInt(4, po.getId_postulante());
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}

	public void eliminar(int id) throws DAOExcepcion {
		System.out.println("PostulanteDAO: eliminar(Int id) "+ id);
		String query = "update postulante set estado=0 where id_persona = ?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, id);

			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo eliminar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}

      public Postulante obtenerPosxId(int id) throws DAOExcepcion {
		
		Postulante vo = new Postulante();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			String query = "SELECT id_postulante, id_persona,nacionalidad, estado_civil, foto,estado " 
						  +"FROM postulante where id_persona=? and estado=1";
			stmt = con.prepareStatement(query);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next()) {

				vo.setId_postulante(rs.getInt("id_postulante"));
				vo.setId_persona(rs.getInt("id_persona"));
				vo.setNacionalidad(rs.getString("nacionalidad"));
				vo.setEstado_civil(rs.getString("estado_civil"));
				vo.setFoto(rs.getString("foto"));
				vo.setEstado(rs.getInt("estado"));
				
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		System.out.println("id generado "+vo.getId_postulante());
		return vo;
	}

     public Postulante obtenerPosxId1(int id) throws DAOExcepcion {
		
		Postulante vo = new Postulante();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			String query = "SELECT id_postulante, id_persona,nacionalidad, estado_civil, foto,estado " 
						  +"FROM postulante where id_persona=?";
			stmt = con.prepareStatement(query);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next()) {

				vo.setId_postulante(rs.getInt("id_postulante"));
				vo.setId_persona(rs.getInt("id_persona"));
				vo.setNacionalidad(rs.getString("nacionalidad"));
				vo.setEstado_civil(rs.getString("estado_civil"));
				vo.setFoto(rs.getString("foto"));
				vo.setEstado(rs.getInt("estado"));
				
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		System.out.println("id generado "+vo.getId_postulante());
		return vo;
	}

     @SuppressWarnings("null")
 	public ArrayList <PosRubro> PostulantexRubro (int of, String rubro) throws DAOExcepcion {
   
    	 ArrayList <PosRubro> list=null;
     	
 		System.out.println("Postulante Rubro "+ of +" "+ rubro);
 		String query = "SELECT pos.id_postulante as idpos, p.id_persona as idper, concat(p.nombres,' ' ,p.ape_paterno,' ', p.ape_materno) as nombre, fec_nacimiento as fecnac, " +
 				"p.tipo_documento as tipodoc, p.nro_documento as numdoc, p.celular as cel,p.correo as mail, d.estudios as estudios, d.fecini as iniest, d.fecfin as finest, " +
 				"d.tipo as tipoest,l.empresa as empresa, l.cargo as cargo, l.fecini as inilab, l.fecfin as finlab" +
 				"FROM postulantexoferta pxo INNER JOIN postulante pos ON pxo.id_postulante=pos.id_postulante " +
 				"INNER JOIN persona p on pos.id_persona=p.id_persona" +
 				"INNER JOIN detalleestudios d ON pos.id_postulante = d.id_postulante" +
 				"INNER JOIN laboral l ON pos.id_postulante=l.id_postulante " +
 				"AND pxo.id_oferta=? and  d.tipo=?";

 		Connection con = null;
 		PreparedStatement stmt = null;
 		ResultSet rs = null;
 		try {
 			con = ConexionBD.obtenerConexion();
 			stmt = con.prepareStatement(query);
 			stmt.setInt(1, of);
 			stmt.setString(2, "%" + rubro + "%");
 			rs = stmt.executeQuery();
 			while (rs.next()) {
 				PosRubro pr= new PosRubro();
 				pr.setIdpos(rs.getInt("idpos"));
 				pr.setIdpos(rs.getInt("idper"));
 				pr.setNombre(rs.getString("nombre"));
 				pr.setFecnac(rs.getTimestamp("fecnac"));
 				pr.setTipdoc(rs.getString("tipodoc"));
 				pr.setNumdoc(rs.getString("numdoc"));
 				pr.setCelular(rs.getString("cel"));
 				pr.setCorreo(rs.getString("mail"));
 				pr.setEstudios(rs.getString("estudios"));
 				pr.setIniest(rs.getTimestamp("iniest"));
 				pr.setFinest(rs.getTimestamp("finest"));
 				pr.setTipoest(rs.getString("tipoest"));
 				pr.setEmpresa(rs.getString("empresa"));
 				pr.setCargo(rs.getString("cargo"));
 				pr.setInilab(rs.getTimestamp("inilab"));
 				pr.setFinlab(rs.getTimestamp("finlab"));
 				list.add(pr);
 			}
 		} catch (SQLException e) {
 			System.err.println(e.getMessage());
 			throw new DAOExcepcion(e.getMessage());
 		} finally {
 			this.cerrarResultSet(rs);
 			this.cerrarStatement(stmt);
 			this.cerrarConexion(con);
 		}
 	   	
     	return list;
     }


}
